<?php

// Initiate connection
$dbCon = @DbConnect($dbServer, $dbName, $dbUser, $dbPassword);


// Connect to DB and return the connection.
// Returns NULL on failure.
function DbConnect($server, $name, $user, $password)
{
  $con = @mysql_connect($server, $user, $password);
  $db = @mysql_select_db($name, $con);
  if(!$con || !$db)
    return NULL;
  else
  {
    mysql_query("SET NAMES 'utf8'", $con);
    return $con;
  }
}

// Disconnect form DB.
function DbDisconnect($server, $name, $user, $password)
{
  $res = mysql_close($server, $user, $password);
  if(!$res)
    return false;
  else
    return true;
}

/**
 * Checks that the connection is alive, and if not, creates a new one.
 * @global string $dbServer
 * @global string $dbName
 * @global string $dbUser
 * @global string $dbPassword
 * @global Object $dbCon
 * @param boolean $reconnect - TRUE to reconnect in case that the connection is down.
 * @return boolean - TRUE if a connection is live (either the old connection that
 *                   was live, or the newly created one).
 */
function DbCheckConnection($reconnect = false)
{
  global $dbServer, $dbName, $dbUser, $dbPassword, $dbCon;
  $logger = GetLogger(LOG_IMPORT_WIKI_ITEMS);
  $alive = @mysql_ping($dbCon);
  $logger->LogInfo("alive=$alive, reconnect=$reconnect");
  if (!$alive && $reconnect === true)
  {
    @DbDisconnect($dbServer, $dbName, $dbUser, $dbPassword);
    $dbCon = @DbConnect($dbServer, $dbName, $dbUser, $dbPassword);
    $alive = (bool) $dbCon;
    $logger->LogInfo("after reconnection alive=$alive");
  }
  return $alive;
}


// Executes a given query and returns the rows 
// as an array of associative arrays of the form (column -> value)
function &ExecuteQuery($query)
{
  // debug memory usage on localhost
  if (IS_DEVELOPMENT_SERVER)
  {
    // if memory used is over 
    $memoryUsed = memory_get_usage(true) / (1028 * 1028);
    $existingLimit = intval(ini_get('memory_limit'));
    if ($memoryUsed > $existingLimit - 30)
    {
      LogMemoryUsage('ExecuteQuery');
      IncreasePhpMemoryLimit('200M');
    }
  }
  
  $timer = StartDebugTimer();
  global $dbCon;
  $result = array();
  $rows = mysql_query($query, $dbCon);
  
  if ($rows === FALSE && LOG_DB_ERRORS)
    LogDbErrorToFile($query);
  
  $numFields = mysql_num_fields($rows);
  $rowNum = 0;
  while ($row = mysql_fetch_array($rows))
  {
    for ($i = 0; $i < $numFields; $i++)
    {
      $fieldName = mysql_field_name($rows, $i);
      $result[$rowNum][$fieldName] = $row[$i];
    }
    $rowNum++;
  }
  DebugQuery($query, $timer, $rowNum);
  return $result;
}

/**
 * Executes a "paginated" SELECT query by adding LIMIT clause and counting the
 * total number of rows (see $paginationData parameter)
 * Note: your query must contain a global SELECT query with no LIMIT clauses
 *      (you cant apply pagination something like SELECT {..} UNION SELECT {...})
 * @param string $query
 * @param Array $paginationData - array that stores pagination data
 *  array (
 *    'from' - row number to limit
 *    'to' - row number to limit
 *    'total' -  total number of rows found
 * )
 * @return <Array> - query result
 *
 * Usage:
 * $paginationData = array('from' => 0, 'to' => 1);
 * $rows = ExecutePaginatedQuery("SELECT * FROM foo", $paginationData);
 * var_dump($rows) // will output one row
 * var_dump($paginationData['total']) - will output total number of rows in foo table
 */
function &ExecutePaginatedQuery(&$query, &$paginationData = array())
{
  $from = intval($paginationData['from']);
  $to = intval($paginationData['to']);
  $count = $to - $from;
  $limit = '';
  if (intval($count) > 0)
    $limit = " LIMIT $from, $count";
  else if ($from > 0)
    $limit = " LIMIT $from, 18446744073709551615"; // To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. (mysql manual)

  $query = trim($query);
  // if query starts with SELECT statement we can insert SQL_CALC_FOUND_ROWS prefix.s
  if (stripos($query, "SELECT ") === 0)
  {
    $query = substr($query, 7);
    // as stated in mysql 5 manual, the best way to find total number of rows is
    // to add SQL_CALC_FOUND_ROWS prefix
    $result = ExecuteQuery("SELECT SQL_CALC_FOUND_ROWS $query $limit");

    // get the total number of rows in a separate query.
    $rows = ExecuteQuery("SELECT FOUND_ROWS() AS total");
    $paginationData['total'] = $rows[0]['total'];
  }
  else
    // otherwise dont count total number of rows
    $result = ExecuteQuery("$query $limit");

  return $result;
}


// Executes a query that returns no data (e.g. UPDATE, INSERT).
// Return true on success, and false on failure.
function ExecuteNonQuery($query)
{
  $timer = StartDebugTimer();
  global $dbCon;
  $res = mysql_query($query, $dbCon);
  if ($res === FALSE)
    if (LOG_DB_ERRORS)
      LogDbErrorToFile($query);
  DebugQuery($query, $timer);
  if ($res)
    return true;
  else
    return false;
}

function SelectRow($table, $columns, $where=1, $suffix='')
{
  if(!isset($where)) $where = 1;
  $query = "SELECT $columns FROM `$table` WHERE $where $suffix";
  return ExecuteQuery($query); 
}

function SearchRows($table, $columns, $match, $against, $where=1)
{
  if(!isset($where)) $where = 1;
  if ($against)
    $query = "SELECT $columns FROM `$table` WHERE $where AND MATCH($match) AGAINST(\"$against\" IN BOOLEAN MODE)";
  else 
    $query = "SELECT $columns FROM `$table` WHERE $where";
  return ExecuteQuery($query);
}

function GetLastInsertId($table)
{
  $res = ExecuteQuery("SELECT LAST_INSERT_ID() as id FROM `$table` LIMIT 0,1");
  return $res[0]['id'];
}

function FormatSqlString($value)
{
  return mysql_real_escape_string($value);
}
function ToSqlQuotedString($value)
{
  return "'".FormatSqlString($value)."'";
}

/**
 * Formats timestamp to mysql date string
 * @param integer $timestamp
 * @return string
 */
function FormatSqlDate($timestamp)
{
  return date('Y-m-d', $timestamp);
}

/**
 * Formats timestamp to datetime string
 * @param integer $timestamp
 * @return string
 */
function FormatSqlDatetime($timestamp)
{
  return date('Y-m-d H:i:s', $timestamp);
}

function InsertRow($table, $values, $querySuffix)
{
  $strColumns = '';
  $strValues = '';
  foreach ($values as $column=>$value)
  {
    $column = trim($column);
    if(is_null($value))
      $value = 'NULL';
    else
      $value = ToSqlQuotedString($value);

    $strColumns .= ("$strColumns" != '' ? ',' : '') . "`$column`";
    $strValues .= ($strValues != '' ? ',' : '') . $value;
  }
  $query = "INSERT INTO `$table` ($strColumns) values ($strValues) $querySuffix";
  return ExecuteNonQuery($query);
}

/**
 * Inserts multiple rows into the database
 * @param string $table
 * @param array $rowsValues - array of dictionaries of values for each row
 *            array(
 *              0 => array(<column> => <unescaped value>) // row
 *              1 => array(<column> => <unescaped value>) // row
 *            )
 * @return boolean
 */
function InsertRows($table, $rowsValues, $querySuffix)
{
  $columns = array();
  $strRowsValues = array();
  foreach ($rowsValues[0] as $column => $value)
    $columns[] =  '`' . trim($column) . '`';
  $colsCount = count($columns);
  foreach ($rowsValues as $rowValues)
  {
    // check the number of columns
    if (count($rowValues) != $colsCount)
      return false;
    
    $strRowValues = '';
    foreach ($rowValues as $column => $value)
    {
      if (is_null($value))
        $value = 'NULL';
      else
        $value = ToSqlQuotedString($value);
      $strRowValues .= ($strRowValues != '' ? ',' : '') . $value;
    }
    $strRowsValues[] = $strRowValues;
  }
  $query = "INSERT INTO `$table` (" . implode(", ", $columns). ") "
          ." VALUES (" . implode("), (", $strRowsValues) . ") $querySuffix";
  return ExecuteNonQuery($query);
}

/**
 * @param type $table
 * @param type $values
 * @return type
 */
function ReplaceRow($table, $values)
{
  $strColumns = '';
  $strValues = '';
  foreach ($values as $column=>$value)
  {
    $column = trim($column);
    if (is_null($value))
      $value = 'NULL';
    else
      $value = ToSqlQuotedString($value);

    $strColumns .= ($strColumns != '' ? ',' : '') . $column;
    $strValues .= ($strValues != '' ? ',' : '') . $value;
  }
  $query = "REPLACE INTO `$table` ($strColumns) values ($strValues)";
  return ExecuteNonQuery($query);
}

/**
 * Inserts multiple rows into the database
 * @param string $table
 * @param array $rowsValues - array of dictionaries of values for each row
 *            array(
 *              0 => array(<column> => <unescaped value>) // row
 *              1 => array(<column> => <unescaped value>) // row
 *            )
 * @return boolean
 */
function ReplaceRows($table, $rowsValues)
{
  $columns = array();
  $strRowsValues = array();
  foreach ($rowsValues[0] as $column => $value)
    $columns[] =  '`' . trim($column) . '`';
  $colsCount = count($columns);
  foreach ($rowsValues as $rowValues)
  {
    // check the number of columns
    if (count($rowValues) != $colsCount)
      return false;
    
    $strRowValues = '';
    foreach ($rowValues as $column => $value)
    {
      if (is_null($value))
        $value = 'NULL';
      else
        $value = ToSqlQuotedString($value);
      $strRowValues .= ($strRowValues != '' ? ',' : '') . $value;
    }
    $strRowsValues[] = $strRowValues;
  }
  $query = "REPLACE INTO `$table` (" . implode(", ", $columns). ") "
          ." VALUES (" . implode("), (", $strRowsValues) . ")";
  return ExecuteNonQuery($query);
}


/**
 * @param type $table
 * @param type $values
 * @param int $where
 * @return type
 */
function UpdateRow($table, $values, $where = 1)
{
  $strValues = '';
  if(!isset($where)) $where = 1;
  foreach ($values as $column=>$value)
  {
    $column = trim($column);
    if(is_null($value))
      $value = 'NULL';
    else
      $value = ToSqlQuotedString($value);
      
    $strValues .= ($strValues != '' ? ',' : '') . "`$column` = $value";
  }
  $query = "UPDATE `$table` SET $strValues WHERE $where";
  return ExecuteNonQuery($query);
}

/**
 * @param type $table
 * @param int $where
 * @return type
 */
function DeleteRow($table, $where=1)
{
  if(!isset($where)) $where = 1;
  $query = "DELETE FROM `$table` WHERE $where";
  return ExecuteNonQuery($query);
}

/**
 * @global Object $dbCon
 * @param type $table
 * @param int $where
 * @return boolean
 */
function RowExists($table, $where=1)
{
  $timer = StartDebugTimer();
  global $dbCon;
  if(!isset($where)) $where = 1;
  $query = "SELECT * FROM `$table` WHERE $where LIMIT 1";
  $rows = mysql_query($query, $dbCon);
  DebugQuery($query, $timer);
  if (mysql_num_rows($rows) > 0)
    return true;
  else
  {
    return false;
  }
}

/**
 * @param type $tableName
 * @return type
 */
function GetTableColumns($tableName)
{
  // get columns names
  $query = "SHOW COLUMNS FROM `$tableName`";
  $rows = ExecuteQuery($query);
  $columns = array();
  foreach ($rows as $row)
    $columns[] = $row['Field'];
  return $columns;
}

/**
 * @param type $tableName
 * @param type $columnName
 * @return type
 */
function ColumnExists($tableName, $columnName)
{
  $res = false;
  $timer = StartDebugTimer();
  global $dbCon;
  global $dbName;
  $columns  = GetTableColumns($tableName);
  return in_array($columnName, $columns);
}

/**
 * @param type $tableName
 * @return type
 */
function TableExists($tableName)
{
  global $dbName;
  $timer = StartDebugTimer();
  $query = "SELECT DISTINCT table_name FROM information_schema.tables "
          . " WHERE table_name=" . ToSqlQuotedString($tableName)
          . " AND table_schema=" . ToSqlQuotedString($dbName);
  $rows = ExecuteQuery($query);
  DebugQuery($query, $timer);
  return (count($rows) > 0 && $rows[0]['table_name'] == $tableName);
}

/**
 * @param type $oldName
 * @param type $newName
 */
function RenameTable($oldName, $newName)
{
  $oldName = mysql_real_escape_string($oldName);
  $newName = mysql_real_escape_string($newName);
  $query = " ALTER TABLE `$oldName` RENAME TO `$newName`";
  ExecuteNonQuery($query);
}

/**
 * @global Object $dbCon
 * @param type $table
 * @param int $where
 * @return type
 */
function CountRows($table, $where = 1)
{
  $timer = StartDebugTimer();
  // select count(*) is faster than select count(column)
  global $dbCon;
  if (!isset($where))
    $where = 1;
  $query = "SELECT COUNT(*) AS count FROM $table WHERE $where";
  $rows = mysql_query($query, $dbCon);
  $row = mysql_fetch_array($rows);
  DebugQuery($query, $timer);
  return intval($row['count']);
}

/**
 * @param type $table
 * @param type $type
 * @return type
 */
function LockTable($table, $type)
{
  $query = "LOCK TABLES `$table` $type";
  return ExecuteNonQuery($query);
}

/**
 * @return type
 */
function UnlockTables()
{
  return ExecuteNonQuery("UNLOCK TABLES");
}

/**
 * @param type $bool
 * @return type
 */
function BoolToSqlBit($bool)
{
  return $bool===true ? 1 : 0;
}

/**
 * @param type $number
 * @return type
 */
function SqlBitToBool($number)
{
  return ($number === 1 || $number === '1') ? true : false;
}

/**
 * Builds SQL IN clause "$column IN ($values[0], $values[1],...)" or "FALSE" if
 *            $values is an empty array.
 * @param String $column
 * @param Array<String> $values
 * @return <type>
 */
function BuildSqlInClause($column, $values)
{
  if (is_array($values) && count($values) > 0)
  {
    $where = '';
    foreach ($values as $value)
      if ((string) trim($value) !== '')
        $where .= ($where == '' ? '' : ', ') . ToSqlQuotedString($value);
    if ($where)
      return "$column IN ($where)";
  }
  return " FALSE";
}

/**
 * Gets ORDER BY clause from incoming pagination data on model classes.
 * @param Array $columnMapping - dictionary of <model-property> => <column-name>
 * @param Array $paginationData - 'dir', 'sort' and 'page' parameters needed on
 *              a paginated list of model class instances.
 * @param String $prefix - table preffix
 * @return <type>
 */
function BuildOrderByClause($columnMapping, $paginationData, $prefix = null)
{
  $dir = strtolower($paginationData['dir']) == "asc" ? "asc" : "desc";
  $sort = $paginationData['sort'];
  $orderClause = "";
  if (array_key_exists($sort, $columnMapping))
  {
    // don't escape $column since it may contain table prefix
    $column = $columnMapping[$sort];
    if ($prefix)
      $prefix .= '.';
    $orderClause = " ORDER BY {$prefix}{$column} {$dir}";
  }
  return $orderClause;
}

/**
 * Verifies that all query parts are valid strings and adds a leading comma to $columns, if defined.
 * @param string $where
 * @param string $joins
 * @param string $suffix
 * @param Array $columns 
 * @param Array $properties 
 * @param String $prefix 
 */
function PrepareQueryPartsOnModel(&$where, &$joins, &$suffix, &$columns, &$properties, &$prefix = '')
{
  if (!is_string($where) || $where === "")
    $where = "1";
  if (!is_string($joins))
    $joins = "";
  if (!is_string($suffix))
    $suffix = "";

  if (is_array($columns))
  {
    $columnsArr = $columns;
    $columns = array();
    $properties = array();
    foreach ($columnsArr as $column => $columnData)
    {
      if ($columnData['alias'])
        $columns[] = $column . ' AS ' . $columnData['alias'];
      if ($columnData['property'])
        $properties[$columnData['alias']] = $columnData['property'];
    }
    $columns = implode(', ', $columns);
  }
  if (!is_string($columns) || $columns === "")
    $columns = "";
  else
    $columns = ', ' . $columns;

  // if no LEFT JOINs fround 'SELECT ALL' will have the same effect as 'SELECT DISTINCT',
  // so that we can use 'ALL' to increase performance.
  $prefix = 'ALL';
  if (preg_match('/LEFT[ ]+JOIN /', $joins))
    $prefix = 'DISTINCT';
}

/**
 * Loads dynamic properties on an model class instance and fills them with property values.
 * @param <type> $obj - model class instance
 * @param <type> $properties - dictionary of <database-column> => <property-name>
 * @param <type> $propertyValues  - dictionary of <database-column> => <value>
 */
function SetDynamicPropertiesOnModel($obj, $properties, $propertyValues)
{
  // set dynamic properties
  foreach ($properties as $column => $propertyName)
  {
    if (is_string($propertyName) && strlen($propertyName) > 0)
    {
      $obj->$propertyName = $propertyValues[$column];
    }
  }
}

/**
 * Saves property on all models in a single mysql query. (Note: models muse have Id property defined)
 * @param type $models
 * @param type $table
 * @param type $propertyName
 * @param type $column
 */
function SavePropertyValuesOnModels($models, $table, $propertyName, $column)
{
  $valuesDict = array();
  $dbValuesDict = array();
  $indexesDict = array();
  foreach ($models as $model)
  {
    $valueKey = (string) $model->$propertyName;
    $index = (int) $indexesDict[$valueKey];
    $valuesDict[$valueKey][$index][] = $model->id;
    $dbValuesDict[$valueKey] = $model->$propertyName;
    if (count($valuesDict[$valueKey][$index]) > 200)
      $indexesDict[$valueKey]++;
  }

  foreach ($valuesDict as $valueKey => $values)
    foreach ($values as $index => $ids)
      UpdateRow($table, array($column => $dbValuesDict[$valueKey]), BuildSqlInClause('id', $ids));
}

function GroupModelIdsInChunks($ids)
{
  $groupedIds = array();
  $length = 0;
  $index = 0;
  $ids = array_values(array_unique($ids));
  for ($i = 0, $count = count($ids); $i < $count; $i++)
  {
    $groupedIds[$index][] = $ids[$i];
    $length++;
    if ($length > 200)
    {
      $index++;
      $length = 0;
    }
  }
  return $groupedIds;
}

function LogDbErrorToFile($query)
{
  $backtrace = debug_backtrace();
  $msg = "MySQL ".mysql_errno(). ': '.mysql_error();
  if ($query !== null)
    $msg .= "\nquery: $query";
  if (LOG_DB_ERRORS_BACKTRACE) {
    foreach ($backtrace as $key => $value) {
      $msg .= "\n[{$key}]File -> ".$value['file'].' (line: '.$value['line'].') function -> '.$value['function'];
    }
  }
  $logger = GetLogger(LOG_DATABASE_ERRORS, KLogger::ERROR);
  $logger->LogError($msg);
}

function StartDebugTimer()
{
  return microtime(true);
}

function DebugQuery($query, $startTime, $resultsCount = null)
{
  
  static $DATABASE_EXECUTION_TOTAL_TIME = 0;
  static $PHP_EXECUTION_TOTAL_TIME = 0;
  static $DATABASE_QUERIES = 0;
  static $lastCall = 0;
  $DATABASE_QUERIES += 1;
  
  $endTime = microtime(true);
  if (DEBUG_DB_QUERIES === true)
  {
    $elapsedQuery = round(($endTime - $startTime) * 1000, 2);
    $DATABASE_EXECUTION_TOTAL_TIME += $elapsedQuery;
    // log also time from the last DebugQuery call to find possible bottlenecks in
    // the php code besides database queries.
    $strTotal = '';
    if ($lastCall)
    {
      $elapsedLastCall = round(($endTime - $lastCall) * 1000, 2);
      $elapsedPhp = $elapsedLastCall - $elapsedQuery;
      $PHP_EXECUTION_TOTAL_TIME += $elapsedPhp;
      $strTotal = " (php: {$elapsedPhp}ms, dbtotal: {$DATABASE_EXECUTION_TOTAL_TIME}ms, phptotal: {$PHP_EXECUTION_TOTAL_TIME}ms), ";
    }
    $lastCall = $endTime;
    
    $backtrace = debug_backtrace();
    $data = array_shift($backtrace);
    $prefix = preg_replace('/system.*$/', '', $data['file']);
    $strBacktrace = array();
    for ($i = 0; $i < count($backtrace) && $i < 4; $i++)
    {
      $data = $backtrace[$i];
      $strBacktrace[] = ($data['class'] ? $data['class'] . '::' : '') . $data['function'] . '()' 
                        . " in " . str_replace($prefix, '', $data['file']) . ":" . $data['line'];

      if (!preg_match('/database|models/', $data['file']))
        break;
    }
    $strBacktrace = implode(', ', $strBacktrace);
    
    $msg = '';
    // $msg .= "NUM: $DATABASE_QUERIES, TIME: {$elapsedQuery}ms,$strTotal" ;
    $msg .= "TIME: {$elapsedQuery}ms, " ;
    if ($resultsCount > 0)
      $msg .= "ROWS: " . $resultsCount . ", " ;
    $msg .= "QUERY: " . trim(substr($query, 0, 2000)) .  " ";
    $msg .= "TRACE: $strBacktrace " ;
    
    $logger = GetLogger(LOG_DATABASE_QUERIES);
    $logger->LogInfo($msg);
  }

  if ($endTime - $startTime > 0.100)
  {
    $backtrace = debug_backtrace();
    $data = array_shift($backtrace);
    $prefix = preg_replace('/system.*$/', '', $data['file']);
    $strBacktrace = array();
    for ($i = 0; $i < count($backtrace) && $i < 10; $i++)
    {
      $data = $backtrace[$i];
      $strBacktrace[] = ($data['class'] ? $data['class'] . '::' : '') . $data['function'] . '()' 
                        . " in " . str_replace($prefix, '', $data['file']) . ":" . $data['line'];
    }
    $strBacktrace = implode(', ', $strBacktrace);

    $elapsedQuery = round(($endTime - $startTime) * 1000, 2);
    $msg = '';
    $msg .= "TIME: {$elapsedQuery}ms, " ;
    if ($resultsCount > 0)
      $msg .= "ROWS: " . $resultsCount . ", " ;
    $msg .= "QUERY: " . trim(substr($query, 0, 2000)) .  " ";
    $msg .= "TRACE: $strBacktrace " ;

    $logger = GetLogger(LOGS_PATH . '/database/long-queries.txt');
    $logger->LogInfo($msg);
  }
}

?>
